CREATE       PROCEDURE [dbo].[asi_CreateDeferredIncomeWork]
@financialEntityKey uniqueidentifier
AS
DECLARE @finEntityCode nvarchar(50)
DECLARE @orgKey uniqueidentifier
/*     This stored procedure will do the following:
*/
SELECT @finEntityCode = fe.FinancialEntityCode, @orgKey = fe.OrganizationKey
FROM FinancialEntity fe
WHERE FinancialEntityKey = @financialEntityKey

DELETE DeferredIncomeWork
WHERE FinancialEntityCode = @finEntityCode AND OrganizationKey = @orgKey
INSERT DeferredIncomeWork
(InvoiceKey, InvoiceNumber, EffectiveDate, FinancialEntityCode, OrganizationKey, DeferredIncomeGLAccountCode,
TargetGLAccountCode, AmountDeferred, DeferralTermsKey, GLTransactionLineKey,
InvoiceLineKey, InvoiceLineNumber, TotalAmountToBeDeferred)
SELECT glm.InvoiceKey, im.InvoiceNumber, im.InvoiceDate, @finEntityCode, @orgKey, gla1.GLAccountCode,
gla2.GLAccountCode, -1 * glt.Amount, glt.DeferralTermsKey, glt.GLTransactionLineKey, glt.InvoiceLineKey,
il.InvoiceLineNumber, il.ExtendedPrice
FROM GLTransactionLine glt
INNER JOIN GLTransactionMain glm ON glt.GLTransactionKey = glm.GLTransactionKey
INNER JOIN InvoiceMain im ON im.InvoiceKey = glm.InvoiceKey
INNER JOIN InvoiceLine il ON il.InvoiceLineKey = glt.InvoiceLineKey
INNER JOIN GLAccount gla1 ON gla1.GLAccountKey = glt.GLAccountKey
INNER JOIN GLAccount gla2 ON gla2.GLAccountKey = glt.TargetGLAccountKey
WHERE glt.DeferralProcessedFlag = 0
AND glt.TargetGLAccountKey is not null
UPDATE GLTransactionLine SET DeferralProcessedFlag = 1
WHERE GLTransactionLineKey IN (SELECT GLTransactionLineKey from DeferredIncomeWork
WHERE FinancialEntityCode = @finEntityCode AND OrganizationKey = @orgKey)

GO